#!/usr/bin/env python3


import configparser

import MySQLdb
from DBUtils import PooledDB


class DBPool:

    __pool = None

    def __init__(self, config_file="ox.ini"):
        """
        读取配置文件
        """
        config = configparser.ConfigParser()
        config.readfp(open(config_file))

        host = config.get("database", "ip")
        user = config.get("database", "user")
        db = config.get("database", "db")
        port = int(config.get("database", "port"))
        passwd = config.get("database", "passwd")
        mincached = int(config.get("database", "mincached"))
        maxcached = int(config.get("database", "maxcached"))

        # 创建连接池
        self.__pool = PooledDB.PooledDB(MySQLdb, mincached=mincached,
                maxcached=maxcached, host=host, user=user,
                passwd=passwd, db=db, port=port, charset='utf8')

    def pool_get_connection(self):
        """
        获取连接句柄
        """
        if self.__pool is None:
            return None
        return self.__pool.connection()



# 定义全局变量
g_db_pool = DBPool("ox.ini")
#  空变量
NULLS = ['', None, [], (), {}]



class CMySQLPort:
    """
    数据库用一个全局变量
    """
    conn = None
    cur = None

    def __init__(self, pool=g_db_pool):
        """
        构造函数中获取连接句柄
        """
        self.conn = pool.pool_get_connection()
        if self.conn is None:
            return
        self.cur = self.conn.cursor()
        self.cur.connection.autocommit(True)


    def __del__(self):
        """
        析构函数
        """
        if self.cur:
            self.cur.close()

        if self.conn:
            self.conn.close()

    def sql_execute(self, sql_command, **kwargs):
        """
        执行SQL语句
        """
        try:
            nRet = self.cur.execute(sql_command, **kwargs)
            self.sql_commit()
            return nRet
        except MySQLdb.Error as e:
            #  OXTRACE(LOG_FILE, "sql_call_proc {%s} except %d %s" % (sql_command, e.args[0], e.args[1]))
            print( "sql_execute except %d %s", e.args[0], e.args[1] )
            pass

    def sql_select_db(self, db):
        """
        选择数据库
        """
        try:
            self.conn.select_db(db)
        except MySQLdb.Error as e:
            print( "sql_select_db except %d %s", e.args[0], e.args[1] )
            pass

    def sql_get_row_count(self):
        """
        获取查询记录条数
        """
        return self.cur.rowcount

    def sql_commit(self):
        """
        提交修改记录
        """
        try:
            self.conn.commit()
        except MySQLdb.Error as e:
            print( "sql_commit except %d %s", e.args[0], e.args[1] )

    def sql_call_proc(self, funName, *param):
        """
        执行存储过程
        """
        try:
            nRet = self.cur.callproc(funName, param)
            # nRet = self.cur.execute( "call SP_UpdateUserHead();", param )
            return nRet
        except MySQLdb.Error as e:
            print( "sql_call_proc except %d %s", e.args[0], e.args[1] )

    def sql_fetch_one(self):
        try:
            return self.cur.fetchone()
        except MySQLdb.Error as e:
            print( "sql_fetchone except %d %s", e.args[0], e.args[1] )
            pass

    def sql_fetch_all(self):
        try:
            return self.cur.fetchall()
        except MySQLdb.Error as e:
            print( "sql_fetchall except %d %s", e.args[0], e.args[1] )

    def sql_reset(self):
        self.cur.close()
        self.cur = self.conn.cursor()

    def close_db(self):
        self.cur.close()
        self.cur = None
        self.conn.close()
        self.conn = None

    def sql_next_set(self):
        self.cur.nextset()


class ParseSql:

    def __init__(self):
        self.sql = dict(
                insert="""insert into {tbName}{fields} values{values};""",
                delete="""delete from {tbName} where {conditions};""",
                update="""update {tbName} set {kvs} where {conditions};""",
                select = "select {columns} from {tbName} where {conditions};""")

    def parse_v(self, v, ch='"'):
        """
        解析数据库操作值, 避免同时出现单引号和双引号
        """
        return str(v).replace(ch, '\\%s' % ch)

    def gen_fv(self, **kwargs):
        """
        insert类型存储过程参数拼接
        """
        fields = list()
        values = list()

        for k, v in kwargs.items():
            if v in NULLS:
                continue

            fields.append(k)
            values.append("'%s'" % self.parse_v(v, '\''))

        fields = '(%s)' % ','.join(fields)
        values = '(%s)' % ','.join(values)

        return fields, values

    def gen_kvs(self, seq=',', **kargs):
        """
        update/delete类型存储过程参数拼接
        """
        f = lambda k, v: '%s="%s"' % (k, self.parse_v(v))
        kvs = [f(k, v) for k, v in kargs.items() if v not in NULLS]

        return seq.join(kvs)

    def parser_insert(self, tb_name, data_dict):
        """
        执行insert语句
        """
        fields, values = self.gen_fv(**data_dict)
        print(fields, values)
        sql = self.sql['insert'].format(tbName=tb_name, fields=fields, values=values)
        print(sql)

        return sql

    def parser_delete(self, tb_name, data_dict):
        """
        执行delete语句
        """
        ks_vs = gen_kvs(' and ', **data_dict)
        print(ks_vs)
        sql = self.sql['delete'].format(tbName=tb_name, conditions=ks_vs)
        print(sql)

        return sql

    def parser_update(self, tb_name, data_dict, conds):
        """
        执行update语句
        """
        ks_vs = self.gen_kvs(**data_dict)
        conds = self.gen_kvs(' and ', **conds)
        print(ks_vs, conds)
        sql = self.sql['update'].format(tbName=tb_name, kvs=ks_vs,
                conditions=conds)
        print(sql)

        return sql

    def parser_select(self, tb_name, ks, conds):
        """
        执行select语句
        """
        ks_vs = ','.join([x for x in ks])
        conds = self.gen_kvs(' and ', **conds) if isinstance(conds, dict) else conds
        print(ks_vs, conds)
        sql = self.sql['select'].format(tbName=tb_name, columns=ks_vs,
                conditions=conds)
        print(sql)

        return sql

    def run(self, func, *args):
        func = getattr(self, func)
        return func(*args)



class ExecuteSql:

    def __init__(self):
        self.handler = CMySQLPort()

    def execute_command(self, sqlType, *args):
        """
        执行sql语句,返回语句执行结果和游标中获取的数据集
        """
        self.handler.sql_reset()
        func = 'parser_' + sqlType
        command = ParseSql().run(func, *args)
        ret = self.handler.sql_execute(command)
        data = self.handler.sql_fetch_all()

        return ret, data

    def execute_proc(self, spName, *args):
        """
        执行存储过程，返回游标中获取的结果
        """
        self.handler.sql_reset()
        self.handler.sql_call_proc(spName, *args)
        data = self.handler.sql_fetch_all()

        return data




if __name__ == "__main__":
    #  csql = CMySQLPort()
    #  sql = "select * from user;"
    #  nRecord = csql.sql_execute(sql)
    #  data = csql.sql_fetch_all()
    #  print( data )
    #  csql.close_db()
    ret, data = ExecuteSql.execute_command('select', 'user', ['id', 'name', 'age'])
    print(ret)
